﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace acessaDB
{
    public class conectaDBMSSQLServer
    {
        public void abrirConexao(SqlConnection conexao)
        {
            try
            {
                if (conexao.State == System.Data.ConnectionState.Closed)
                    conexao.Open();
            }
            catch
            { }
        }

        public void fecharConexao(SqlConnection conexao)
        {
            try
            {
                if (conexao.State == System.Data.ConnectionState.Open)
                    conexao.Close();
            }
            catch
            { }
        }

        public DataSet retornaDataSet(string strQuery, string connectionString)
        {
            DataSet dsRetorno = new DataSet();
            SqlConnection SqlConexao = new SqlConnection(connectionString);
            SqlCommand SqlComando = new SqlCommand();
            SqlDataAdapter fbAdapter = new SqlDataAdapter();
            try
            {
                abrirConexao(SqlConexao);
                SqlComando.CommandType = CommandType.Text;
                SqlComando.CommandText = strQuery;
                SqlComando.Connection = SqlConexao;
                SqlComando.ExecuteNonQuery();

                fbAdapter.SelectCommand = SqlComando;
                fbAdapter.Fill(dsRetorno);

                return dsRetorno;
            }
            finally
            {
                fecharConexao(SqlConexao);
                dsRetorno.Dispose();
                SqlConexao.Dispose();
                SqlComando.Dispose();
                fbAdapter.Dispose();
            }
        }

        public SqlDataReader retornaDataReader(string strQuery, string connectionString, SqlConnection SqlConexao)
        {
            SqlCommand SqlComando = new SqlCommand();
            try
            {
                abrirConexao(SqlConexao);
                SqlComando.CommandType = CommandType.Text;
                SqlComando.CommandText = strQuery;
                SqlComando.Connection = SqlConexao;
                SqlDataReader drRetorno = SqlComando.ExecuteReader();
                return drRetorno;
            }
            finally
            {
                //fecharConexao(SqlConexao);     ***** PARA OBJETOS DATAREADER, A CONEXAO DEVE SER FECHADA, NO METODO QUE O CHAMA *****
                //SqlConexao.Dispose();                  ********** SOMENTE APOS A VARRADURA DO MESMO *********** 
                //SqlComando.Dispose();
                //fbAdapter.Dispose();
            }
        }

        //Classe para execução de comando
        public string _ExecutarComandoRetornandoID(string strQuery, string connectionString, bool comRetornoID = true, string usu_login = "SYSTEM")
        {
            SqlConnection SqlConexao = new SqlConnection(connectionString);
            string idObjeto = "0";
            try
            {
                abrirConexao(SqlConexao);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = strQuery.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = SqlConexao;
                cmd.ExecuteNonQuery();

                //metodo de gravação de auditoria
                if (comRetornoID == true)
                {
                    string tableName = _sqlStatementeTable(strQuery.ToUpper());
                    if (!string.IsNullOrEmpty(tableName))
                    {
                        SqlCommand ident = new SqlCommand("SELECT MAX(" + tableName + "_ID) FROM " + tableName + " AS idTabela", SqlConexao);
                        SqlDataReader ident_result = ident.ExecuteReader();
                        try { idObjeto = ident_result.GetDecimal(0).ToString(); }
                        catch { idObjeto = ""; }

                        if (ident_result.Read())
                        {
                            try
                            {
                                return ident_result.GetDecimal(0).ToString();
                            }
                            catch (Exception)
                            {
                                return "0";
                            }
                        }
                    }
                }

                return "0";
            }

            finally
            {
                fecharConexao(SqlConexao);
                auditoria_dml(strQuery, idObjeto, usu_login, connectionString);
            }
        }

        public string _ExecutarStoredProcedure(string procedureName,
                                               string[] parameters,
                                               string connectionString,
                                               string usu_login = "SYSTEM",
                                               string parametroRetorno = "0")
        {
            SqlConnection SqlConexao = new SqlConnection(connectionString);
            try
            {
                abrirConexao(SqlConexao);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = SqlConexao;
                SqlParameter parametros = new SqlParameter();
                if (parametroRetorno != "0")
                {
                    parametros = cmd.Parameters.Add(parametroRetorno, SqlDbType.Int);
                    parametros.Direction = ParameterDirection.ReturnValue;
                }
                foreach (string myParam in parameters)
                {
                    parametros = cmd.Parameters.Add(myParam, SqlDbType.Int, 12);
                }

                //cmd.CommandText = strQuery.ToString();
                cmd.ExecuteNonQuery();

                return "0";
            }

            finally
            {
                fecharConexao(SqlConexao);
            }
        }

        public void auditoria_dml(string strQuery, string idObjeto, string usuario, string connectionString)
        {
            SqlConnection SqlConexao = new SqlConnection();
            try
            {
                abrirConexao(SqlConexao);
                if (!string.IsNullOrEmpty(usuario))
                {
                    strQuery = strQuery.ToUpper().Trim();
                    string action = "";
                    string tabela = "";
                    string values = "''";
                    if ((string.IsNullOrEmpty(idObjeto)) || (idObjeto == "0")) idObjeto = "NULL";
                    string audStatement = "''";

                    if (strQuery.IndexOf("INSERT") > -1)
                    {
                        strQuery = strQuery.Replace("INSERT", "").Trim();
                        strQuery = strQuery.Replace("INTO", "").Trim();
                        tabela = strQuery.Substring(0, strQuery.IndexOf(" ", 0));
                        strQuery = strQuery.Substring(strQuery.IndexOf("VALUES", 0), strQuery.Length - strQuery.IndexOf("VALUES", 0));
                        values = "'" + strQuery.Replace("VALUES", "") + "'";
                        action = "'INSERT'";
                    }
                    else if (strQuery.IndexOf("UPDATE") > -1)
                    {
                        strQuery = strQuery.Replace("UPDATE ", "");
                        tabela = strQuery.Substring(0, strQuery.IndexOf(" ", 0));
                        //
                        values = strQuery.Substring(strQuery.IndexOf("SET", 0), strQuery.Length - strQuery.IndexOf("SET", 0)).Replace("SET", "");
                        values = "'" + strQuery.Remove(strQuery.IndexOf("WHERE", 0)) + "'";
                        values = values.Replace(tabela, "");
                        values = values.Replace("SET", "");
                        //
                        strQuery = strQuery.Substring(strQuery.IndexOf("WHERE", 0), strQuery.Length - strQuery.IndexOf("WHERE", 0));
                        audStatement = "'" + strQuery + "'";
                        action = "'UPDATE'";
                    }
                    else if (strQuery.IndexOf("DELETE") > -1)
                    {
                        strQuery = strQuery.Replace("DELETE FROM ", "");
                        tabela = strQuery.Substring(0, strQuery.IndexOf(" ", 0));
                        strQuery = strQuery.Substring(strQuery.IndexOf("WHERE", 0), strQuery.Length - strQuery.IndexOf("WHERE", 0));
                        audStatement = "'" + strQuery + "'";
                        action = "'DELETE'";
                    }

                    if ((!string.IsNullOrEmpty(tabela)) && (!string.IsNullOrEmpty(action)))
                    {
                        try
                        {
                            SqlCommand cmdAudit = new SqlCommand();
                            cmdAudit.CommandText = "EXECUTE [dbo].[SP_AUDITORIA_DML] '" +
                                                    usuario + "', '" +
                                                    tabela + "', " +
                                                    idObjeto + ", " +
                                                    action + ", " +
                                                    audStatement + ", " +
                                                    values;
                            cmdAudit.CommandType = CommandType.Text;
                            cmdAudit.Connection = SqlConexao;
                            cmdAudit.ExecuteNonQuery();
                        }
                        catch { }
                    }
                }
            }
            finally
            {
                fecharConexao(SqlConexao);
            }
        }

        //funcao para retorna o nome da TABLE modificdada
        private string _sqlStatementeTable(string sql)
        {
            string result = string.Empty;
            if (sql.IndexOf("INSERT INTO") > 0)
            {
                sql = sql.Replace("INSERT INTO", "");

            }
            else if (sql.IndexOf("UPDATE") > 0)
            {
                sql = sql.Replace("UPDATE", "");

            }
            string[] arraySql = sql.Split(new char[] { ' ' });
            result = arraySql[0];
            return result.Trim();
        }

    }
}